using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// Class for data acces layer 
/// </summary>
/// 
public class Dal
{
    static public ArrayList arParams=new ArrayList();
    static private SqlConnection objConnection;
    private static String sConnectionString = (string)ConfigurationManager.ConnectionStrings["RondoConnectionString"].ConnectionString;
    public static DataTable ExecuteDBsp(String SPname)
    {
        //String sParams = GetStringFromParameters(arParameters, false);
        return GetData(SPname,string.Empty);
    }

    public static DataSet ExecuteDBspDS(String SPname)
    {
        //String sParams = GetStringFromParameters(arParameters, false);
        return GetDataSet(SPname, string.Empty);
    }

    public static DataSet ExecuteDBspDS(String SPname,ArrayList arParameters)
    {
        String sParams = GetStringFromParameters(arParameters, false);
        return GetDataSet(SPname, sParams);
    }

    public static int ExecuteDBspNonQuery(String SPname,ArrayList arParameters)
    {
        String sParams = GetStringFromParameters(arParameters, false);
        return NonQueryData(SPname, sParams);
    }

    public static object ExecuteDBspOneValue(String SPname, ArrayList arParameters)
    {
        String sParams = GetStringFromParameters(arParameters, false);
        return GetScalar(SPname, sParams);
    }

    public static object ExecuteDBspOneValue(String SPname, ArrayList arParameters, bool bNVARCHAR)
    {
        String sParams = GetStringFromParameters(arParameters, bNVARCHAR);
        return GetScalar(SPname, sParams);
    }

    public static DataTable ExecuteDBsp(String SPname, ArrayList arParameters)
    {
        String sParams = GetStringFromParameters(arParameters, false);
        return GetData(SPname,sParams);
    }

    public static DataTable ExecuteDBsp(String SPname, ArrayList arParameters,bool bNVARCHAR)
    {
        String sParams = GetStringFromParameters(arParameters, bNVARCHAR);
        return GetData(SPname, sParams);
    }

    private static DataTable GetData(String SPname,String sParams)
    {

        
        objConnection = new SqlConnection(sConnectionString);

        //check current connection state
        try
        {
            if (objConnection.State == System.Data.ConnectionState.Closed)
                objConnection.Open();

            SqlDataAdapter objRecordSet = new SqlDataAdapter(SPname + " " + sParams, objConnection);

            DataSet DS = new DataSet();

            try
            {
                //objRecordSet.Fill(DS, "TotalRecords");
                //int RecordsCount = DS.Tables["TotalRecords"].Rows.Count;
                DS.Clear();
                objRecordSet.Fill(DS, "Table");
                objRecordSet = null;

            }

            catch { }

            objRecordSet = null;

            if (objConnection.State == System.Data.ConnectionState.Open)

                objConnection.Close();

            return DS.Tables["Table"];
        }
        catch { return null; }
    }

    private static DataSet GetDataSet(String SPname, String sParams)
    {

        objConnection = new SqlConnection(sConnectionString);

        //check current connection state
        try
        {
            if (objConnection.State == System.Data.ConnectionState.Closed)
                objConnection.Open();

            SqlDataAdapter objRecordSet = new SqlDataAdapter(SPname + " " + sParams, objConnection);

            DataSet DS = new DataSet();

            try
            {
                //objRecordSet.Fill(DS, "TotalRecords");
                //int RecordsCount = DS.Tables["TotalRecords"].Rows.Count;
                DS.Clear();
                objRecordSet.Fill(DS, "Table");
                objRecordSet = null;

            }

            catch { }

            objRecordSet = null;

            if (objConnection.State == System.Data.ConnectionState.Open)

                objConnection.Close();

            return DS;
        }
        catch { return null; }
    }

    private static int NonQueryData(String SPname, String sParams)
    {

        objConnection = new SqlConnection(sConnectionString);

        int iReturn=-1;
        //check current connection state
        try
        {
            if (objConnection.State == System.Data.ConnectionState.Closed)
                objConnection.Open();

            SqlDataAdapter objRecordSet = new SqlDataAdapter(SPname + " " + sParams, objConnection);
            iReturn=objRecordSet.SelectCommand.ExecuteNonQuery();

            if (objConnection.State == System.Data.ConnectionState.Open)

                objConnection.Close();

        }
        catch {  }
        return iReturn;
    }

    private static object GetScalar(String SPname, String sParams)
    {

        objConnection = new SqlConnection(sConnectionString);

        object oReturnObject=null;
        //check current connection state
        try
        {
            if (objConnection.State == System.Data.ConnectionState.Closed)
                objConnection.Open();

            SqlDataAdapter objRecordSet = new SqlDataAdapter(SPname + " " + sParams, objConnection);
            oReturnObject = objRecordSet.SelectCommand.ExecuteScalar();

            if (objConnection.State == System.Data.ConnectionState.Open)

                objConnection.Close();

        }
        catch { }
        return oReturnObject;
    }
    /// <summary>
    /// Gets the string from parameters.
    /// </summary>
    /// <param name="arParameters">parameters</param>
    /// <param name="bNVARCHAR">if set to <c>true</c> [b NVARCHAR].</param>
    /// <returns></returns>
    private static String GetStringFromParameters(ArrayList arParameters, Boolean bNVARCHAR)
    {
        String sParams = String.Empty;
        if (arParameters != null)
        {
            if (arParameters.Count > 0)
            {
                for (Int32 i = 0; i < arParameters.Count; i++)
                {
                    if (arParameters[i] != null)
                    {
                        switch (arParameters[i].GetType().Name.ToLower())
                        {
                            case "int":
                            case "int32":
                            case "int64":
                            case "long":
                                sParams += arParameters[i].ToString();
                                break;
                            case "string":
                                String sN = (bNVARCHAR) ? "N" : String.Empty;
                                sParams += sN + "'" + arParameters[i].ToString().Replace("'", "`") + "'";
                                break;
                            case "bool":
                            case "boolean":
                                    sParams += ((bool)arParameters[i])?1:0;
                                break;
                            default:
                                sParams += "''";
                                break;
                        }
                    }
                    else
                        sParams += "''";

                    if (i < arParameters.Count - 1)
                        sParams += ",";

                }
            }
        }
        return sParams;
    }
}
